const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'stumsg'
})

routes.route('/')
    .get( (req,resp)=>{

        //获取cookies
        var uid = req.cookies.uid;
        var gender = req.cookies.gender;
        //获取当前教师的专业id
        var sub_id = req.cookies.sub_id;


        //console.log(age)
        //判断
        if(gender == 1){
            var pname = '教师';
        }else {
            var pname = '学生';
        }
        //console.log(sub_id)
        //每一页把条数据
        var param = 6;
        //将当前的页码传递出去作比较
        //如果首次登录教师账号  params 为空的情况下让它自动生成一个值 然后直接与下方list进行数学运算 默认查询第一页的数据
        var pos = (req.query.params) ? req.query.params : 1;
        //console.log(pos)
        //获取查询的页条数的起始位置
        var list = (pos * param) - param;

        // console.log(list)
        //    执行sql语句
        var sql = `SELECT stu_list.uid , stu_list.stu_name ,stu_list.age , stu_list.gender , stu_list.birtmp FROM stu_list WHERE stu_list.sub_id = ${sub_id} LIMIT ${list}, ${param}`;
        //console.log(sql)
        //查询总条数
        var sqll = `SELECT COUNT(*) as sum FROM stu_list WHERE stu_list.sub_id = ${sub_id}`;
        //console.log(sql)
        pool.getConnection(function(err,connection){
            if(err) throw err;
            //    链接成功
            connection.query(sql,(error,result)=> {
                //console.log(result)
                //    链接成功
                connection.query(sqll,(errs,res)=> {

                    //    释放连接   关闭数据库
                    connection.release();
                    var arr = [];

                    //获取当前的系统时间戳
                    var dat = new Date().getTime();
                    //console.log(dat)
                    //提取当前时间戳的年份
                    var year = new Date(dat).getFullYear();
                    //将查询的时间戳进行年龄计算
                    result.forEach( (item , index)=>{
                        arr.push( year - new Date(item.birtmp).getFullYear() )
                    } )

                    //console.log(arr)

                    //提取出来总条数
                    var sum = res[0].sum;

                    //判断需要多少页  条数不能为小数点  需要向上取整
                    var params = Math.ceil( sum / param );
                    //console.log(params)
                    if(result){
                        //
                        resp.render('sel_list',{
                            uid : uid,
                            pname : pname,
                            result : result,
                            params : params,  //总页数
                            pos : pos,  //当前页数
                            arr : arr,  //重新计算数据库的年龄
                        })
                    }
                    //console.log(result)
                    //sql语句出错执行报错处理
                    if(errs) throw errs;
                })
                //sql语句出错执行报错处理
                if(error) throw error;
            })

        })
    } )

//暴露接口
module.exports = routes;